﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Net;
using System.Net.Mail;
using CS2102_Music.Logic.LogicModels;

namespace CS2102_Music.Logic
{
    public partial class Logic
    {
        // Functions with SQL
        public bool CheckIfIsExistingUser(string email)
        {
            bool existing = false;
            List<string> users = new List<string>();
            string name;

            MySqlDataReader result = databaseConnector.ExecuteReaderQuery("select u.name from users u where email = @email"
                                                                            , new QueryParameter("@email", email)
                                                                            );
            while (result.Read())
            {
                name = result.GetString(0);
                users.Add(name);
            }

            result.Close();

            if (users.Count != 0)
            {
                existing = true;
            }
            else
            {
                existing = false;
            }

            return existing;
        }
        public bool CreateUser(string name, string gender, string password, string DOB, string email, string type)
        {
            bool create = false;
            string hashedPassword;
            string date = Utilities.ToSQLDateString(DOB);

            // hash password
            hashedPassword = CreateHash(password);

            // insert into table

            create = databaseConnector.ExecuteNonQuery("insert into users(email, name, DOB, password, gender, isAdmin)  values (@email, @name, @DOB, @password, @gender, @isAdmin)"
                                                        , new QueryParameter("@email", email)
                                                        , new QueryParameter("@name", name)
                                                        , new QueryParameter("@DOB", DOB)
                                                        , new QueryParameter("@password", hashedPassword)
                                                        , new QueryParameter("@gender", gender)
                                                        , new QueryParameter("@isAdmin", type)
                                                      );

            return create;
        }
        public UserL GetUser(string email)
        {
            UserL user = new UserL();

            MySqlDataReader result = databaseConnector.ExecuteReaderQuery("select u.name, u.DOB, u.password, u.gender, u.isAdmin from users u where email = @email"
                                                                           , new QueryParameter("@email", email)
                                                                           );

            //result's tuple is: categoryID, generalCategoryID
            while (result.Read())
            {
                user.Email = email;
                user.Name = result.GetString(0);
                user.DOB = result.GetDateTime(1);
                user.Password = result.GetString(2);
                user.Gender = result.GetString(3);
                user.IsAdmin = result.GetString(4);            
            }

            result.Close();

            return user;
        }
        public bool UserLogIn(string email, string password)
        {
            bool logIn = false;
            string password_data = "";
            MySqlDataReader result = databaseConnector.ExecuteReaderQuery("select u.password from users u where email = @email"
                                                                          , new QueryParameter("@email", email)
                                                                          );


            while (result.Read())
            {
                password_data = result.GetString(0);
            }

            result.Close();

            logIn = MatchHash(password_data, password);

            return logIn;
        }
        public bool UpdateUser(string name, string gender, string DOB, string email, string password, bool updatePassword)
        {
            bool update = false;

            if (updatePassword)
            {
                string hashedPassword;
                hashedPassword = CreateHash(password);
                update = databaseConnector.ExecuteNonQuery("UPDATE users SET password = @password WHERE email = @email"
                                                           , new QueryParameter("@password", hashedPassword)
                                                           , new QueryParameter("@email", email)
                                                           );
            }


            update = databaseConnector.ExecuteNonQuery("UPDATE users SET name = @name WHERE email = @email"
                                                       , new QueryParameter("@name", name)
                                                       , new QueryParameter("@email", email)
                                                       );

            update = databaseConnector.ExecuteNonQuery("UPDATE users SET gender = @gender WHERE email = @email"
                                                      , new QueryParameter("@gender", gender)
                                                      , new QueryParameter("@email", email)
                                                      );

            update = databaseConnector.ExecuteNonQuery("UPDATE users SET DOB = " + Utilities.ToSQLDateString(DOB) + " WHERE email = @email"
                                                         , new QueryParameter("@email", email)
                                                         );
            return update;
        }

        public List<Spent> TotalSpendCustomer()
        {
            List<Spent> total = new List<Spent>();
            Spent one = new Spent();

            string query = "SELECT bs.buyer, ROUND(SUM(bs.purchase_price)+SUM(ba.purchase_price), 2) FROM buysong bs, buyalbum ba WHERE bs.buyer = ba.buyer GROUP BY bs.buyer ORDER BY ROUND(SUM(bs.purchase_price)+SUM(ba.purchase_price), 2) DESC";

            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query);
                     
            //result's tuple is: categoryID, generalCategoryID
            while (result.Read())
            {
                one.customer = result.GetString(0);
                one.spent = result.GetDouble(1);

                total.Add(one);

                one = new Spent();
            }

            result.Close();

            return total;
        }

    }
}